import math
import re

import xlrd
from openpyxl import Workbook
from openpyxl.styles import Font, colors, Alignment
from model.UniversityPlanScore import UniversityPlanScore
from model.UniversityPlanScoreError2 import UniversityPlanScoreError2
from model.UniversityScoreRank import UniversityScoreRank
from model.UniversityMajorRank import UniversityMajorRank
from model.UniversityPlanScoreError import UniversityPlanScoreError
from model.University import University
from model.OneScoreRank import OneScoreRank
import sys


# 导出《高考指南》错误数据，然后手动校正
def export_score():
    universityPlanScoreError = UniversityPlanScoreError()
    data = universityPlanScoreError.getList(2021)
    # print(len(data), data)
    # sys.exit()
    wb = Workbook()
    ws = wb.create_sheet(title='Sheet1')
    ws.row_dimensions[1].height = 27
    ws['A1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['C1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['D1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['E1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['F1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['G1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['H1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['I1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['J1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['K1'].alignment = Alignment(horizontal='left', vertical='center')
    ws['L1'].alignment = Alignment(horizontal='left', vertical='center')
    ws.column_dimensions['A'].width = 10
    ws.column_dimensions['B'].width = 10
    ws.column_dimensions['C'].width = 10
    ws.column_dimensions['D'].width = 10
    ws.column_dimensions['E'].width = 15
    ws.column_dimensions['F'].width = 15
    ws.column_dimensions['G'].width = 15
    ws.column_dimensions['H'].width = 15
    ws.column_dimensions['I'].width = 15
    ws.column_dimensions['J'].width = 15
    ws.column_dimensions['K'].width = 15
    ws.column_dimensions['L'].width = 150
    ws['A1'] = 'ID'
    ws['B1'] = '年份'
    ws['C1'] = '页码'
    ws['d1'] = '批次'
    ws['E1'] = '院校代码'
    ws['F1'] = '院校名称'
    ws['G1'] = '科目组及专业名称'
    ws['H1'] = '专业类型'
    ws['I1'] = '计划数'
    ws['J1'] = '投档数'
    ws['K1'] = '录取数'
    ws['L1'] = '分数分布'
    i = 2
    for row in data:
        print(row)
        ws.row_dimensions[i].height = 27
        ws['A' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['B' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['C' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['D' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['E' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['F' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['G' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['H' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['I' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['J' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['K' + str(i)].alignment = Alignment(horizontal='left', vertical='center')
        ws['L' + str(i)].alignment = Alignment(horizontal='left', vertical='center')

        ws['A' + str(i)] = row['cid']
        ws['B' + str(i)] = row['year']
        ws['C' + str(i)] = row['page']
        if row['batch'] == 9:
            ws['d' + str(i)] = '本科第一批'
        elif row['batch'] == 10:
            ws['d' + str(i)] = '本科第一批预科批'
        elif row['batch'] == 11:
            ws['d' + str(i)] = '本科第二批'
        elif row['batch'] == 13:
            ws['d' + str(i)] = '本科第二批预科A类'
        elif row['batch'] == 14:
            ws['d' + str(i)] = '本科第二批预科B类'
        elif row['batch'] == 19:
            ws['d' + str(i)] = '普通高职高专批'
        else:
            ws['d' + str(i)] = ''
        ws['E' + str(i)] = row['ucode']
        ws['F' + str(i)] = row['uname']
        ws['G' + str(i)] = row['major']
        if row['major_type_2'] == 1:
            ws['H' + str(i)] = '理科'
        else:
            ws['H' + str(i)] = '文科'
        ws['I' + str(i)] = row['plan_number']
        ws['J' + str(i)] = row['submit_number']
        ws['K' + str(i)] = row['enter_number']
        ws['L' + str(i)] = row['score']
        i = i + 1
    dest_filename = '../static/excel/error20220608.xlsx'
    wb.save(filename=dest_filename)


# 导入《高考指南》错误数据
def import_score():
    data = xlrd.open_workbook('../static/excel/error20220608.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    universityPlanScoreError = UniversityPlanScoreError()
    for i in range(rowNum):
        row = dict()
        if i > 0:
            for j in range(colNum):
                if j == 0:
                    row['cid'] = table.cell(i, j).value
                if j == 1:
                    row['year'] = 2021
                if j == 2:
                    row['page'] = int(table.cell(i, j).value)
                if j == 3:
                    row['batch'] = match_code(table.cell(i, j).value)
                if j == 4:
                    row['ucode'] = int(table.cell(i, j).value)
                if j == 5:
                    row['uname'] = table.cell(i, j).value
                if j == 6:
                    row['major'] = table.cell(i, j).value
                if j == 8:
                    if table.cell(i, j).value:
                        row['plan_number'] = int(table.cell(i, j).value)
                    else:
                        row['plan_number'] = 0
                if j == 9:
                    if table.cell(i, j).value:
                        row['submit_number'] = int(table.cell(i, j).value)
                    else:
                        row['submit_number'] = 0
                if j == 10:
                    if table.cell(i, j).value:
                        row['enter_number'] = int(table.cell(i, j).value)
                    else:
                        row['enter_number'] = 0
                if j == 11:
                    row['score'] = table.cell(i, j).value.replace('\n', '')

            print(row)
            universityPlanScoreError.toUpdate(row)


def match_code(code):
    if code == '本科第一批':
        ucode = 9
    elif code == '本科第一批预科批':
        ucode = 10
    elif code == '本科第二批':
        ucode = 11
    elif code == '精准脱贫专项计划本科批':
        ucode = 12
    elif code == '本科第二批预科A类':
        ucode = 13
    elif code == '本科第二批预科B类':
        ucode = 14
    elif code == '高职高专普通批':
        ucode = 19
    else:
        ucode = 0
    return ucode


def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass
    try:
        import unicodedata
        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass

    return False


""" 
根据《高考指南》错误数据（university_plan_score_error），整理筛选出每个院校（文理科）、每个具体专业的分数。并存入university_score_rank、university_major_rank
"""
def do_score_detail():
    universityPlanScoreError = UniversityPlanScoreError()
    universityPlanScoreError2 = UniversityPlanScoreError2()
    universityScoreRank = UniversityScoreRank()
    universityMajorRank = UniversityMajorRank()
    data = universityPlanScoreError.getList(2021)
    for row in data:
        print(row['id'])

        # 去掉空格，将 字母l 替换成 数字1
        score_str = row['score'].replace(" ", "").replace("l", "1")
        # 最后一位是否为符号，不是符号则补全
        if len(score_str) >= 5:
            match_res = re.findall(r"(\d)", score_str[-1])
            if match_res:
                score_str = score_str + '.'

        # 第一位是否为符号，是则去掉
        match_res = re.findall(r"(\d)", score_str[0:1])
        # print(match_res)
        if match_res:
            pass
        else:
            score_str = score_str[1:]
        p_list = []
        s3 = []
        for index, char in enumerate(score_str):

            match_res = re.findall(r"(\d)", char)
            if match_res:
                pass
            else:
                # 第一个字符如果是标点符号，直接跳过
                if index:
                    p_list.append(index)
        # print('score_str', score_str)
        # print('p_list', len(p_list), p_list)
        if p_list:
            start_cut_index = 0
            for i in range(len(p_list)):
                # print(start_cut_index)
                # print(p_list[i])
                s_slice = score_str[start_cut_index:p_list[i]]
                start_cut_index = p_list[i] + 1
                s3.append(s_slice)
        # print("s3", s3)
        while '' in s3:
            s3.remove('')
        # print("s3", s3)
        score = s3[::2]
        num = s3[1::2]
        while '' in score:
            score.remove('')
        print('score', len(score), score)
        print('num', len(num), num)
        university = University()
        university_row = university.getOneByCode(row['ucode'])
        university_score_rank = dict()
        university_score_rank['year'] = row['year']
        university_score_rank['batch'] = row['batch']
        university_score_rank['type'] = row['major_type_2']
        university_score_rank['code'] = row['ucode']
        university_score_rank['name'] = row['uname']
        university_score_rank['score'] = row['score']
        university_score_rank['major'] = row['major']
        if university_row:
            # university_score_rank['type_name'] = university['type_name']
            university_score_rank['f211'] = university_row['f211']
            university_score_rank['f985'] = university_row['f985']
            university_score_rank['dual_class_name'] = university_row['dual_class_name']
            university_score_rank['nature_name'] = university_row['nature_name']
            university_score_rank['province_code'] = university_row['province_code']
            university_score_rank['city_code'] = university_row['city_code']
            university_score_rank['province'] = university_row['province']
            university_score_rank['city'] = university_row['city']
        else:
            # university_score_rank['type_name'] = ''
            university_score_rank['f211'] = 0
            university_score_rank['f985'] = 0
            university_score_rank['dual_class_name'] = 0
            university_score_rank['nature_name'] = 0
            university_score_rank['province_code'] = 0
            university_score_rank['city_code'] = 0
            university_score_rank['province'] = ''
            university_score_rank['city'] = ''
        # 计算平均分、最低分（最后一个算最低分）
        lowest_score = 0
        total_score = 0
        total_person = 0
        error_1 = 0
        if score:
            for k, val in enumerate(score):
                if k <= len(num) - 1 and is_number(val) and is_number(num[k]):
                    score_int = int(val)
                    person_int = int(num[k])
                    if 180 <= score_int < 750 and 0 < person_int < 300:
                        # print(score_int, person_int)
                        if k == len(num) - 1:
                            lowest_score = score_int
                        total_score = total_score + score_int * person_int
                        total_person = total_person + person_int
                    else:
                        if error_1 == 0:
                            print('error_2')
                            # 分数超过3位数或者录取人数超过300
                            insert_error = dict()
                            insert_error['cid'] = row['id']
                            insert_error['year'] = row['year']
                            insert_error['page'] = row['page']
                            insert_error['batch'] = row['batch']
                            insert_error['ucode'] = row['ucode']
                            insert_error['uname'] = row['uname']
                            insert_error['major'] = row['major']
                            insert_error['major_type_2'] = row['major_type_2']
                            insert_error['plan_number'] = row['plan_number']
                            insert_error['submit_number'] = row['submit_number']
                            insert_error['enter_number'] = row['enter_number']
                            insert_error['score'] = row['score']
                            insert_error['remark'] = '分数不合理'
                            universityPlanScoreError2.toInsert(insert_error)
                            error_1 = error_1 + 1
                else:
                    if error_1 == 0:
                        print('error_1')
                        # 分数分布分隔后不相等或者分数存在其他字符
                        insert_error = dict()
                        insert_error['cid'] = row['id']
                        insert_error['year'] = row['year']
                        insert_error['page'] = row['page']
                        insert_error['batch'] = row['batch']
                        insert_error['ucode'] = row['ucode']
                        insert_error['uname'] = row['uname']
                        insert_error['major'] = row['major']
                        insert_error['major_type_2'] = row['major_type_2']
                        insert_error['plan_number'] = row['plan_number']
                        insert_error['submit_number'] = row['submit_number']
                        insert_error['enter_number'] = row['enter_number']
                        insert_error['score'] = row['score']
                        insert_error['remark'] = '分数分布分隔后不相等'
                        universityPlanScoreError2.toInsert(insert_error)
                        error_1 = error_1 + 1

        # 计算最低分，最低排名，平均分、平均排名
        oneScoreRank = OneScoreRank()
        university_score_rank['lowest_score'] = lowest_score
        lowest_rank = oneScoreRank.getOneByScore(university_score_rank['lowest_score'], row['year'],
                                                 row['major_type_2'])
        if lowest_rank:
            university_score_rank['lowest_rank'] = lowest_rank['total_num']
        else:
            university_score_rank['lowest_rank'] = 0

        # 第一种平均分算法
        if total_score and total_person:
            university_score_rank['mean_score'] = math.ceil(total_score / total_person)
            mean_rank = oneScoreRank.getOneByScore(university_score_rank['mean_score'], row['year'],
                                                   row['major_type_2'])
            if mean_rank:
                university_score_rank['mean_rank'] = mean_rank['total_num']
            else:
                university_score_rank['mean_rank'] = 0
        else:
            university_score_rank['mean_score'] = 0
            university_score_rank['mean_rank'] = 0
        if row['plan_number']:
            university_score_rank['plan_number'] = row['plan_number']
        else:
            university_score_rank['plan_number'] = 0

        if row['submit_number']:
            university_score_rank['submit_number'] = row['submit_number']
        else:
            university_score_rank['submit_number'] = 0

        if row['enter_number']:
            university_score_rank['enter_number'] = row['enter_number']
        else:
            university_score_rank['enter_number'] = 0

        # university_score_rank['is_error'] = error_1
        print('error_1：', error_1, 'insert：', university_score_rank)
        # sys.exit()
        if row['major'] == '理工类' or row['major'] == '文史类':
            # 查询university_score_rank是否有记录，有则修改，无则添加
            count = universityScoreRank.getOneByYearBatch(row['year'], row['batch'], row['major_type_2'], row['ucode'])
            print('院校', count)
            if count:
                # 更新
                print('院校更新')
                universityScoreRank.toUpdate(university_score_rank, count['id'])
            else:
                # 添加
                score_ins = university_score_rank
                del score_ins['major']
                print(score_ins)
                universityScoreRank.toInsert(score_ins)
        else:
            # 查询university_major_rank是否有记录，有则修改，无则添加
            count = universityMajorRank.getOneByYearBatch(row['year'], row['batch'], row['major_type_2'],
                                                          row['ucode'], row['major'])
            print('专业', count)
            if count:
                # 更新
                universityMajorRank.toUpdate(university_score_rank, count['id'])
                print('专业更新')
            else:
                # 添加
                major_ins = university_score_rank
                major_ins['uname'] = university_score_rank['name']
                major_ins['name'] = university_score_rank['major']
                del major_ins['major']
                print(major_ins)
                universityMajorRank.toInsert(major_ins)


if __name__ == "__main__":
    do_score_detail()